package com.tjsinfo.tjsoa.oldOADocument.fawen.bo;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.springframework.jdbc.core.RowMapper;

import com.tjsinfo.tjsoa.Dao.ISpringDao;
import com.tjsinfo.tjsoa.oldOADocument.fawen.vo.FuJianFile;
import com.tjsinfo.tjsoa.oldOADocument.fawen.vo.FaWenInfo;

public class TChaYueWenJianBO implements ITChaYueWenJianBO	{

	private ISpringDao springDao;
	

	public ISpringDao getSpringDao() {
		return springDao;
	}

	public void setSpringDao(ISpringDao springDao) {
		this.springDao = springDao;
	}
	
	public Integer wenyearmax(){
		Integer year = (new Date().getYear() + 1900);
		StringBuffer sql = new StringBuffer("use tjsoa SELECT MAX(table_.wenyear) AS wenyear FROM ( ");
		sql.append("SELECT MAX(YEAR(sys_create_date)) AS wenyear FROM dbo.t_document_send_main AS xingzhengfawen  ");
		sql.append("UNION ");
		sql.append("SELECT MAX(YEAR(sys_create_date)) AS wenyear FROM dbo.t_document_send_main AS dangweifawen  ");
//		sql.append("UNION ");
//		sql.append("SELECT MAX(wenyear) AS wenyear FROM dbo.t_tongzhifawen_neirong AS tongzhifawen  ");
		sql.append(") AS table_ GROUP BY table_.wenyear HAVING  table_.wenyear IS NOT NULL ");
		List<Integer> list =  springDao.querySQL(sql.toString(), new Object[]{}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				return arg0.getObject("wenyear");
			}
			
		});
		if (list.size() > 0 ) {
			year = list.get(0);
		} else {
			;
		}
		return year;
	}
	
	public Integer wenyearmin(){
		Integer year = (new Date().getYear() + 1900);
		StringBuffer sql = new StringBuffer("use tjsoa SELECT MIN(table_.wenyear) AS wenyear FROM ( ");
		sql.append("SELECT MIN(YEAR(sys_create_date)) AS wenyear FROM dbo.t_document_send_main AS xingzhengfawen  ");
		sql.append("UNION ");
		sql.append("SELECT MIN(YEAR(sys_create_date)) AS wenyear FROM dbo.t_document_send_main AS dangweifawen  ");
		sql.append(") AS table_ GROUP BY table_.wenyear HAVING  table_.wenyear IS NOT NULL ");
		List<Integer> list =  springDao.querySQL(sql.toString(), new Object[]{}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				return arg0.getObject("wenyear");
			}
			
		});
		if (list.size() > 0 ) {
			year = list.get(0);
		} else {
			;
		}
		return year;
	}
	
	/**
	 * 文件查询
	 * @param first	 起始行
	 * @param max    行数
	 * @param biaoti 标题
	 * @param wenyear 年度
	 * @return
	 */
	public List<FaWenInfo> selectChaYueWenJian(Integer first,Integer max,String biaoti,String wenyear,String wenhao,String maintypeid){
		
		String sql =  new String("USE tjsoa EXEC dbo.fwchayuewenjian @first = ?,@limit = ?,@biaotiselect = ?,@wenyear = ?,@wenhaotype = ? ,@sortid=?");
		List<FaWenInfo> fawenList = springDao.querySQL(sql, new Object[]{first,max,biaoti,wenyear,wenhao,maintypeid}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
					FaWenInfo faWenInfo = new FaWenInfo();
					faWenInfo.setBiaoti(arg0.getString("biaoti"));
					faWenInfo.setWenyear(arg0.getString("wenyear"));
					faWenInfo.setWenhaotype(arg0.getString("wenhaotype"));
					faWenInfo.setFawendatetime(arg0.getDate("fawendatetime"));
//					faWenInfo.setZhuangtai(arg0.getString("zhuangtai"));
					faWenInfo.setFlowId(arg0.getInt("flowid"));
					faWenInfo.setId(arg0.getString("id"));
//					faWenInfo.setWenhao(arg0.getInt("wenhao"));
					return faWenInfo;
				
			}
			
		});
		
		return fawenList;
	}
	
	/**
	 * 文件查询结果总数
	 * @param biaoti 标题
	 * @param wenyear 年度
	 * @return
	 */
	public Integer selectChaYueWenJianCount(String biaoti,String wenyear,String wenhao,String maintypeid){
		String sql =  new String("USE tjsoa EXEC dbo.fwchayuewenjiancount @biaotiselect = ?,@wenyear = ?,@wenhaotype = ?,@sortid=? ");
		List<Integer> countList = springDao.querySQL(sql, new Object[]{biaoti,wenyear,wenhao,maintypeid}, new RowMapper(){
		
			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				return arg0.getInt("count");
				
			}
			
		});
		
		return countList.get(0);
	}

	/**
	 * 根据id查询文件
	 */
	public FaWenInfo selectOneById(String id) {
		// TODO Auto-generated method stub
		FaWenInfo fawenreturn = new FaWenInfo();
		String sql =  new String("select id,subject,number_type,number_year,number_no,affix_memo,filepath,reserve_2 from dbo.t_document_send_main where id =?");
		List<FaWenInfo> fawenList = springDao.querySQL(sql, new Object[]{id}, new RowMapper(){
			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
					FaWenInfo faWenInfo = new FaWenInfo();
					faWenInfo.setId(arg0.getString("id")); 
					faWenInfo.setBiaoti(arg0.getString("subject"));
					faWenInfo.setFujianname(arg0.getString("affix_memo")); //附件描述
					String filepath=arg0.getString("filepath");
					String filename=filepath.substring(filepath.lastIndexOf("\\")+1, filepath.length()-3)+"DOC";
					faWenInfo.setFilepath(filename); //正文文件名
					String fawenwenhao = arg0.getString("number_type")+ "〔" +arg0.getString("number_year")+"〕"+arg0.getString("number_no")+"号";
					faWenInfo.setFawenwenhao(fawenwenhao);
					faWenInfo.setParentid(arg0.getString("reserve_2")); //附件关联id
					return faWenInfo;
				}
			});   
		
		if (fawenList.size()>0){
			fawenreturn = fawenList.get(0);
			String parentid=fawenreturn.getParentid();
			String sqlfujian =  new String("select id, alias,[file_name] as filename, url_Path from dbo.t_com_file where parent_id =?");
			List<FuJianFile> fujianlist = springDao.querySQL(sqlfujian, new Object[]{parentid}, new RowMapper(){
				public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
					FuJianFile fujian= new FuJianFile();
					fujian.setAlias(arg0.getString("alias"));
					fujian.setFileName(arg0.getString("filename"));
					fujian.setUrlPath(arg0.getString("url_Path"));
					return fujian;
				}
			});
			Set fawenFujians = new HashSet(0);
			for(FuJianFile fujianfile:fujianlist){
				fawenFujians.add(fujianfile);
			}
			fawenreturn.setFawenFujians(fawenFujians);
		}
		
		return fawenreturn;
	}
	
}
